﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace FirstDemo.Models
{
    public class MySQLHelper
    {
        public string source { get; set; }

        /// <summary>
        /// MySQL 带连接字符串的构造函数
        /// </summary>
        /// <param name="source">连接字符串</param>
        public MySQLHelper(string source)
        {
            this.source = source;
        }

        #region 基础用法

        /// <summary>
        /// 使用ExecuteNonQuery函数进行执行一条SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句字符串</param>
        /// <returns>返回影响的行数</returns>
        public int ExecSqlNonQry(string sqlStr)
        {
            int rowCount = 0;
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                using (MySqlCommand cmd = new MySqlCommand(sqlStr, conn))
                {
                    try
                    {
                        conn.Open();
                        rowCount = cmd.ExecuteNonQuery();
                        conn.Close();
                        return rowCount;
                    }
                    catch (MySqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }

            }
        }

        /// <summary>
        /// 使用ExecuteNonQuery函数进行执行多条SQL语句，实现数据库事务
        /// </summary>
        /// <param name="sqlStrs">SQL语句字符串数组</param>
        /// <returns>返回影响的行数</returns>
        public int ExecSqlNonQryTran(string[] sqlStrs)
        {
            int rowCount = 0;
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    conn.Open();
                    var txn = conn.BeginTransaction();
                    cmd.Connection = conn;
                    cmd.Transaction = txn;
                    try
                    {
                        foreach (var item in sqlStrs)
                        {
                            if (item.Trim().Length > 1)
                            {
                                cmd.CommandText = item;
                                rowCount += cmd.ExecuteNonQuery();
                            }
                        }
                        txn.Commit();
                        conn.Close();
                        return rowCount;
                    }
                    catch (MySqlException e)
                    {
                        txn.Rollback();
                        conn.Close();
                        throw e;
                    }
                }

            }
        }

        /// <summary>
        /// 使用ExecuteScalar函数进行执行一条SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句字符串</param>
        /// <returns>返回一个对象，若为SELECT语句则为第一行第一列，若不是则为一个未实例化对象</returns>
        public object ExecSqlScalar(string sqlStr)
        {
            Object obj = null;
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                using (MySqlCommand cmd = new MySqlCommand(sqlStr, conn))
                {
                    try
                    {
                        conn.Open();
                        obj = cmd.ExecuteScalar();
                        conn.Close();
                        return obj;
                    }
                    catch (MySqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 使用ExecuteReader函数进行执行一条SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句字符串</param>
        /// <returns>返回对象</returns>
        public object ExecSqlReader<T>(string sqlStr) where T : new()
        {
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = sqlStr;
                    MySqlDataReader reader = cmd.ExecuteReader();
                    return ReadToObj<T>(reader);
                }
                catch (MySqlException e)
                {
                    conn.Close();
                    throw e;
                }
            }
        }

        /// <summary>
        /// 通过MySqlDataAdapter执行一条语句进行查询
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>通过DataTable返回查询结果</returns>
        public DataTable QueryByAdapter(string sqlStr)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    MySqlDataAdapter adapter = new MySqlDataAdapter(sqlStr, conn);
                    adapter.Fill(dt);
                    conn.Close();
                    return dt;
                }
                catch (MySqlException e)
                {
                    conn.Close();
                    throw e;
                }
            }
        }

        /// <summary>
        /// 通过使用存储过程无返回值模式进行数据库操作
        /// </summary>
        /// <param name="procStr">存储过程名称</param>
        /// <param name="parameters">存储过程参数，参数数组</param>
        public void RunProcNoResp(string procStr, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmdProc = BuildProcCommand(conn, procStr, parameters))
                    {
                        cmdProc.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                catch (MySqlException e)
                {
                    conn.Close();
                    throw e;
                }

            }

        }

        /// <summary>
        /// 通过使用存储过程带有返回值模式进行数据库操作
        /// </summary>
        /// <typeparam name="T">需要转换的对象类型</typeparam>
        /// <param name="procStr">存储过程名称</param>
        /// <param name="parameters">存储过程参数，参数数组</param>
        /// <returns>对象</returns>
        public object RunProcResp<T>(string procStr, params MySqlParameter[] parameters) where T : new()
        {
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmdProc = BuildProcCommand(conn, procStr, parameters))
                    {
                        MySqlDataReader reader = cmdProc.ExecuteReader();
                        return ReadToObj<T>(reader);
                    }
                }
                catch (MySqlException e)
                {
                    conn.Close();
                    throw e;
                }
            }
        }

        #endregion

        #region 拓展用法

        /// <summary>
        /// 通过Id异步删除某表的数据
        /// </summary>
        /// <param name="id">字段Id</param>
        /// <param name="tableName">表名</param>
        /// <returns>影响的行数</returns>
        public async Task<int> DeleteByIdAsync(int id, String tableName)
        {
            int rowCount = 0;
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                MySqlTransaction txn = null;
                try
                {
                    await conn.OpenAsync();
                    txn = await conn.BeginTransactionAsync();
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = $"DELETE FROM {tableName} where id = {id}";
                    rowCount = await cmd.ExecuteNonQueryAsync();
                    await txn.CommitAsync();
                    await conn.CloseAsync();

                }
                catch (MySqlException e)
                {
                    await txn.RollbackAsync();
                    Console.WriteLine("Error code : " + e);
                }
                finally
                {
                    await conn.CloseAsync();
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 通过Id调用存储过程寻找对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="procStr">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>数据对象</returns>
        public T SelectById<T>(string procStr, params MySqlParameter[] parameters) where T : new()
        {
            var ans = new T();
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    MySqlCommand cmdProc = new MySqlCommand(procStr, conn);
                    cmdProc.CommandType = CommandType.StoredProcedure;
                    foreach (var item in parameters)
                    {
                        cmdProc.Parameters.Add(item);
                    }
                    MySqlDataReader reader = cmdProc.ExecuteReader();
                    while (reader.Read())
                    {
                        int i = 0;
                        foreach (var pi in ans.GetType().GetProperties())
                        {
                            pi.SetValue(ans, reader[i++]);
                        }
                    }
                    conn.Close();

                }
                catch (MySqlException e)
                {
                    Console.WriteLine("Error code : " + e);
                }
                finally
                {
                    conn.Close();
                }
            }
            return ans;
        }

        /// <summary>
        /// 通过表名以及传入添加对象进行插入一行数据
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="obj">需要插入的对象</param>
        /// <returns>插入是否成功</returns>
        public bool InsertOne<T>(string tableName, T obj)
        {
            bool isOK = false;
            string sql = $"Insert into {tableName} set ";
            foreach (var temp in obj.GetType().GetProperties())
            {
                if (temp.GetValue(obj) != null)
                {
                    sql += temp.Name + "=" + temp.GetValue(obj) + " ";
                }
            }
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = sql;
                        if (cmd.ExecuteNonQuery() != 0)
                        {
                            isOK = true;
                        }

                    }
                }
                catch (MySqlException e)
                {
                    Console.WriteLine("Error Code : ", e);
                }
                finally
                {
                    conn.Close();
                }
            }
            return isOK;
        }

        /// <summary>
        /// 通过表名以及传入需要修改的对象进行修改一条数据
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="obj">需要修改的对象</param>
        /// <returns>操作是否成功</returns>
        public bool UpdateById<T>(string tableName, T obj)
        {
            bool isOK = false;
            string sql = $"update {tableName} set ";
            foreach (var temp in obj.GetType().GetProperties())
            {
                if (temp.GetValue(obj) != null && temp.Name != "Id")
                {
                    sql += temp.Name + "=" + temp.GetValue(obj) + " ";
                }
            }
            sql += $"where Id = {obj.GetType().GetProperty("Id").GetValue(obj)}";
            using (MySqlConnection conn = new MySqlConnection(source))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = sql;
                        if (cmd.ExecuteNonQuery() != 0)
                        {
                            isOK = true;
                        }

                    }
                }
                catch (MySqlException e)
                {
                    Console.WriteLine("Error Code : ", e);
                }
                finally
                {
                    conn.Close();
                }
            }
            return isOK;
        }

        #endregion

        #region 辅助函数

        /// <summary>
        /// 通过Reader将读到的数据转换为对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="reader">reader</param>
        /// <returns>对象数组</returns>
        public object ReadToObj<T>(MySqlDataReader reader) where T : new()
        {
            var list = new List<T>();
            using (reader)
            {
                while (reader.Read())
                {
                    T item = new T();
                    int i = 0;
                    foreach (var pi in item.GetType().GetProperties())
                    {
                        pi.SetValue(item, reader[i++]);
                    }
                    list.Add(item);
                }
            }
            if (list.Count == 1)
                return list[0];
            else
                return list;
        }

        /// <summary>
        /// 构建MySqlCommand对象，用来执行存储过程
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="procStr">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>MySqlCommand</returns>
        private MySqlCommand BuildProcCommand(MySqlConnection conn, string procStr, MySqlParameter[] parameters)
        {
            MySqlCommand cmdProc = new MySqlCommand(procStr, conn);
            cmdProc.CommandType = CommandType.StoredProcedure;
            foreach (var item in parameters)
            {
                if (item != null)
                {
                    if ((item.Direction == ParameterDirection.InputOutput || item.Direction == ParameterDirection.Input) &&
                        (item.Value == null)) //检查未分配值的输出参数，将其分配为DBNULL.Value
                    {
                        item.Value = DBNull.Value;
                    }
                    cmdProc.Parameters.Add(item);
                }
            }
            return cmdProc;
        }

        #endregion
    }
}
